import pandas as pd
import math
from sqlalchemy import create_engine


def batch_import_excel_to_mysql(excel_file, table_name, field_mapping, batch_size=10):
    # 配置数据库连接
    # username = "root"
    # password = "root"
    # host = "127.0.0.1"
    # database = "wyc_db"

    # 生产环境配置数据库连接
    host = "10.100.1.93"
    username = "ylsk_app"
    password = "dYPuqggg7CLmBIHICE"
    database = "wyc_db"

    port = "3306"  # MySQL默认端口

    engine = create_engine(
        f"mysql+pymysql://{username}:{password}@{host}:{port}/{database}"
    )

    # 读取Excel文件，只读取指定列
    excel_columns = list(field_mapping.keys())
    df = pd.read_excel(excel_file, usecols=excel_columns)

    # 验证字段是否存在
    missing_columns = [col for col in excel_columns if col not in df.columns]
    if missing_columns:
        raise ValueError(f"Excel文件中缺少以下字段: {missing_columns}")

    df = df.rename(columns=field_mapping)

    total_rows = len(df)
    print(f"总共读取到 {total_rows} 行数据")
    print(f"字段映射: {field_mapping}")

    total_batches = math.ceil(total_rows / batch_size)

    try:
        for i in range(total_batches):
            start_idx = i * batch_size
            end_idx = min((i + 1) * batch_size, total_rows)

            batch_df = df.iloc[start_idx:end_idx]

            # 处理空值
            # batch_df = batch_df.fillna('')
            # print('batch_df',batch_df);
            batch_df.to_sql(table_name, con=engine, index=False, if_exists="append")

            print(f"第 {i+1}/{total_batches} 批数据导入成功，共 {len(batch_df)} 条")

        print("所有数据导入完成！")

    except Exception as e:
        print(f"导入过程中发生错误: {str(e)}")

    finally:
        engine.dispose()


if __name__ == "__main__":

    field_mapping = {"名称": "cust_name", "标签": "cust_label"}
    batch_import_excel_to_mysql(
        excel_file="D://work//ylsk//export//药房白名单20251014.xlsx",
        table_name="tb_cust_label_temp",
        field_mapping=field_mapping,
        batch_size=10,
    )
